using Microsoft.Data.Sqlite;
using System.Data;
using System.Security.Cryptography;
using System.Text;

namespace MyBlogServer.Data
{
    public class BlogContext
    {
        private readonly string _connectionString;

        public BlogContext(string connectionString)
        {
            _connectionString = connectionString;
        }

        public IDbConnection CreateConnection()
        {
            return new SqliteConnection(_connectionString);
        }

        public void InitializeDatabase()
        {
            using var connection = CreateConnection() as SqliteConnection;
            connection!.Open();

            var createPostsTable = @"
                CREATE TABLE IF NOT EXISTS Posts (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Title TEXT NOT NULL,
                    Content TEXT NOT NULL,
                    Summary TEXT,
                    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
                    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
                    Published BOOLEAN DEFAULT FALSE,
                    Slug TEXT UNIQUE,
                    Tags TEXT,
                    ViewCount INTEGER DEFAULT 0
                )";

            var createUsersTable = @"
                CREATE TABLE IF NOT EXISTS Users (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Username TEXT UNIQUE NOT NULL,
                    PasswordHash TEXT NOT NULL,
                    Email TEXT,
                    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
                )";

            using var command = connection.CreateCommand();
            command.CommandText = createPostsTable;
            command.ExecuteNonQuery();

            command.CommandText = createUsersTable;
            command.ExecuteNonQuery();
            
            // 为现有的 Posts 表添加 Tags 列（如果不存在）
            try
            {
                command.CommandText = "ALTER TABLE Posts ADD COLUMN Tags TEXT";
                command.ExecuteNonQuery();
            }
            catch (SqliteException ex) when (ex.SqliteErrorCode == 1)
            {
                // 列已存在，忽略错误
            }
            
            // 为现有的 Posts 表添加 ViewCount 列（如果不存在）
            try
            {
                command.CommandText = "ALTER TABLE Posts ADD COLUMN ViewCount INTEGER DEFAULT 0";
                command.ExecuteNonQuery();
            }
            catch (SqliteException ex) when (ex.SqliteErrorCode == 1)
            {
                // 列已存在，忽略错误
            }
            
            // 确保默认用户存在
            EnsureDefaultUserExists(connection);
        }
        
        private void EnsureDefaultUserExists(SqliteConnection connection)
        {
            // Check if admin user exists
            var sql = "SELECT COUNT(*) FROM Users WHERE Username = 'admin'";
            using var command = connection.CreateCommand();
            command.CommandText = sql;
            var userCount = Convert.ToInt32(command.ExecuteScalar());
            
            Console.WriteLine($"检查默认用户是否存在: {userCount}");
            
            if (userCount == 0)
            {
                Console.WriteLine("创建默认用户");
                // Create default admin user
                var insertSql = @"INSERT INTO Users (Username, PasswordHash, Email, CreatedAt)
                                  VALUES (@Username, @PasswordHash, @Email, @CreatedAt)";
                
                using var insertCommand = connection.CreateCommand();
                insertCommand.CommandText = insertSql;
                
                var usernameParam = insertCommand.CreateParameter();
                usernameParam.ParameterName = "@Username";
                usernameParam.Value = "admin";
                insertCommand.Parameters.Add(usernameParam);
                
                var passwordHashParam = insertCommand.CreateParameter();
                passwordHashParam.ParameterName = "@PasswordHash";
                passwordHashParam.Value = HashPassword("admin");
                insertCommand.Parameters.Add(passwordHashParam);
                
                var emailParam = insertCommand.CreateParameter();
                emailParam.ParameterName = "@Email";
                emailParam.Value = "admin@example.com";
                insertCommand.Parameters.Add(emailParam);
                
                var createdAtParam = insertCommand.CreateParameter();
                createdAtParam.ParameterName = "@CreatedAt";
                createdAtParam.Value = DateTime.UtcNow;
                insertCommand.Parameters.Add(createdAtParam);
                
                insertCommand.ExecuteNonQuery();
                Console.WriteLine("默认用户创建成功");
            }
            else
            {
                // 检查现有用户的密码哈希值
                var selectSql = "SELECT Username, PasswordHash FROM Users WHERE Username = 'admin'";
                using var selectCommand = connection.CreateCommand();
                selectCommand.CommandText = selectSql;
                using var reader = selectCommand.ExecuteReader();
                if (reader.Read())
                {
                    var username = reader.GetString(0);
                    var passwordHash = reader.GetString(1);
                    Console.WriteLine($"找到用户: {username}, 密码哈希: {passwordHash}");
                }
                Console.WriteLine("默认用户已存在");
            }
        }
        
        private string HashPassword(string password)
        {
            using var sha256 = SHA256.Create();
            var hashedBytes = sha256.ComputeHash(Encoding.UTF8.GetBytes(password));
            var hash = BitConverter.ToString(hashedBytes).Replace("-", "").ToLower();
            Console.WriteLine($"密码 '{password}' 的哈希值: {hash}");
            return hash;
        }
    }
}